{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {},
   "outputs": [],
   "source": [
    "from py2neo import Graph, Node, Subgraph, Relationship\n",
    "import pandas as pd\n",
    "from tqdm import tqdm\n",
    "import itertools"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## function"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {},
   "outputs": [],
   "source": [
    "def connect_graph():\n",
    "    return Graph(\n",
    "        \"bolt://localhost:7687\",\n",
    "        auth=(\"neo4j\", \"bigben123\")\n",
    "    )\n",
    "\n",
    "\n",
    "def clear_graph(graph):\n",
    "    graph.run('match (n) detach delete n')\n",
    "\n",
    "\n",
    "def create_node(graph, tag, name):\n",
    "    node = Node(tag, name=name)\n",
    "    graph.create(node)\n",
    "\n",
    "\n",
    "def create_node_by_subGraph(graph, nodes):\n",
    "    graph.create(Subgraph(nodes=nodes))\n",
    "\n",
    "\n",
    "def create_relationship_by_subGraph(graph, nodes):\n",
    "    graph.create(Subgraph(relationships=nodes))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 105,
   "metadata": {},
   "outputs": [],
   "source": [
    "food_disease_excel = pd.read_excel('./food-disease.xlsx').values.tolist()\n",
    "food_organ_excel = pd.read_excel('./food-organ.xlsx').values.tolist()\n",
    "food_crowd_excel = pd.read_excel('./food-crowd.xlsx').values.tolist()\n",
    "food_alias_excel = pd.read_excel('./food-alias.xlsx').values.tolist()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Init"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 107,
   "metadata": {},
   "outputs": [],
   "source": [
    "graph = connect_graph()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## create all nodes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "LEN OF DISEASE NODE: 33\n",
      "LEN OF ORGAN NODE: 2\n",
      "LEN OF CROWD NODE: 2\n",
      "LEN OF FOOD NODE: 2718\n"
     ]
    }
   ],
   "source": [
    "all_re = []\n",
    "disease_node = {}\n",
    "food_node = {}\n",
    "organ_node = {}\n",
    "crowd_node = {}\n",
    "alias_node = {}\n",
    "drag_node = {}\n",
    "keshi_node = {}\n",
    "\n",
    "for i in food_disease_excel:\n",
    "    if i[0] not in disease_node.keys():\n",
    "        disease_node[i[0]] = Node('disease', name=i[0])\n",
    "    if i[2] not in food_node.keys():\n",
    "        food_node[i[2]] = Node('food', name=i[2])\n",
    "\n",
    "for i in food_organ_excel:\n",
    "    if i[0] not in organ_node.keys():\n",
    "        organ_node[i[0]] = Node('organ', name=i[0])\n",
    "    if i[2] not in food_node.keys():\n",
    "        food_node[i[2]] = Node('food', name=i[2])\n",
    "        \n",
    "for i in food_crowd_excel:\n",
    "    if i[0] not in crowd_node.keys():\n",
    "        crowd_node[i[0]] = Node('crowd', name=i[0])\n",
    "    if i[2] not in food_node.keys():\n",
    "        food_node[i[2]] = Node('food', name=i[2])\n",
    "        \n",
    "for i in food_alias_excel:\n",
    "    if i[0] not in food_node.keys():\n",
    "        food_node[i[0]] = Node('food', name=i[0])\n",
    "    if i[1] not in food_node.keys():\n",
    "        food_node[i[1]] = Node('food', name=i[1])\n",
    "\n",
    "print(\"LEN OF DISEASE NODE:\", len(disease_node.keys()))\n",
    "print(\"LEN OF ORGAN NODE:\", len(organ_node.keys()))\n",
    "print(\"LEN OF CROWD NODE:\", len(crowd_node.keys()))\n",
    "print(\"LEN OF FOOD NODE:\", len(food_node.keys()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## disease-food"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 109,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in food_disease_excel:\n",
    "    r = Relationship(disease_node[i[0]], i[1], food_node[i[2]])\n",
    "    r['name'] = i[1]\n",
    "    all_re.append(r)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## organ-food"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in food_organ_excel:\n",
    "    r = Relationship(organ_node[i[0]], i[1], food_node[i[2]])\n",
    "    r['name'] = i[1]\n",
    "    all_re.append(r)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## crowd-food"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in food_crowd_excel:\n",
    "    r = Relationship(crowd_node[i[0]], i[1], food_node[i[2]])\n",
    "    r['name'] = i[1]\n",
    "    all_re.append(r)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## food-alias"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {},
   "outputs": [],
   "source": [
    "for i in food_alias_excel:\n",
    "    if i[0] != i[1]:\n",
    "        r = Relationship(food_node[i[0]], 'alias', food_node[i[1]])\n",
    "        r['name'] = 'alias'\n",
    "        all_re.append(r)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## add food propertity"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {},
   "outputs": [],
   "source": [
    "zong = pd.read_excel('./总.xlsx').fillna('None')[['食材','介绍','选购','营养价值','食用效果','存储','烹饪小技巧']].values.tolist()\n",
    "for i in zong:\n",
    "    if i[0] not in food_node.keys():\n",
    "        continue\n",
    "    node = food_node[i[0]]\n",
    "    if i[1]!='None':\n",
    "        node['介绍']=str(i[1]).strip()\n",
    "    if i[2]!='None':\n",
    "        node['选购']=str(i[2]).strip()\n",
    "    if i[3]!='None':\n",
    "        node['营养价值']=str(i[3]).strip()\n",
    "    if i[4]!='None':\n",
    "        node['食用效果']=str(i[4]).strip()\n",
    "    if i[5]!='None':\n",
    "        node['存储']=str(i[5]).strip()\n",
    "    if i[6]!='None':\n",
    "        node['烹饪小技巧']=str(i[6]).strip()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## add sizhi data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 483272/483272 [00:15<00:00, 30557.64it/s]\n"
     ]
    }
   ],
   "source": [
    "sizhi = pd.read_csv(r'./Disease.csv').values.tolist()\n",
    "disease_r = [\n",
    "    \"症状\" # 病症\n",
    "]\n",
    "drag_r = [\n",
    "#     \"常用药品\", # 药物 空格隔开\n",
    "    \"推荐药品\" #药品\n",
    "]\n",
    "crowd_r = [\"易感人群\"] # 多见于....\n",
    "food_r = [\n",
    "    \"宜吃食物\", # 食物\n",
    "    \"忌吃食物\", # 食物\n",
    "    \"推荐食谱\", # 食物\n",
    "    \"禁忌食品\" # 食物\n",
    "]\n",
    "keshi_r = [\n",
    "    \"三级科室分类\", # 科室\n",
    "    \"一级科室分类\", # 科室\n",
    "    \"二级科室分类\", # 科室\n",
    "]\n",
    "shuxing_r = [\n",
    "    \"治疗费用\", # 属性\n",
    "    \"简介\", # 属性\n",
    "    \"病因\", # 属性\n",
    "    \"预防方式\", # 属性\n",
    "    \"治疗周期\", # 属性\n",
    "    \"传染方式\", # 属性\n",
    "    \"治愈率\", # 属性\n",
    "    \"治疗概述\", # 属性\n",
    "    \"患病比例\", # 属性\n",
    "]\n",
    "\n",
    "for i in tqdm(sizhi):\n",
    "    disease_h = i[0].replace(\"[疾病]\", '').replace(\"[症状]\", '')\n",
    "    if disease_h not in disease_node.keys():\n",
    "        disease_node[disease_h] = Node('disease', name=disease_h)\n",
    "        \n",
    "    if i[1] in disease_r:\n",
    "        disease_t = i[2].replace(\"[疾病]\", '').replace(\"[症状]\", '')\n",
    "        if disease_t == disease_h:\n",
    "            continue\n",
    "        if disease_t not in disease_node.keys():\n",
    "            disease_node[disease_t] = Node('disease', name=disease_t)\n",
    "        r = Relationship(disease_node[disease_h], '症状', disease_node[disease_t])\n",
    "        r['name'] = '症状'\n",
    "        all_re.append(r)\n",
    "    elif i[1] in drag_r:\n",
    "        if i[2] not in drag_node.keys():\n",
    "            drag_node[i[2]] = Node('drag', name=i[2])\n",
    "        r = Relationship(disease_node[disease_h], '推荐药品', drag_node[i[2]])\n",
    "        r['name'] = '推荐药品'\n",
    "        all_re.append(r)\n",
    "    elif i[1] in crowd_r:\n",
    "        crowd = i[2].replace(\"多见于\", \"\")\n",
    "        if crowd not in crowd_node.keys():\n",
    "            crowd_node[crowd] = Node('crowd', name=crowd)\n",
    "        r = Relationship(disease_node[disease_h], '易感人群', crowd_node[crowd])\n",
    "        r['name'] = '易感人群'\n",
    "        all_re.append(r)\n",
    "    elif i[1] in keshi_r:\n",
    "        if i[2] not in keshi_node.keys():\n",
    "            keshi_node[i[2]] = Node('medical_department', name=i[2])\n",
    "        r = Relationship(disease_node[disease_h], i[1], keshi_node[i[2]])\n",
    "        r['name'] = i[1]\n",
    "        all_re.append(r)\n",
    "    elif i[1] in shuxing_r:\n",
    "        node = disease_node[disease_h]\n",
    "        value = str(i[2]).strip()\n",
    "        value = value.replace('\\n/g', '\\\\n').replace('\\r/g', '\\\\r')\n",
    "        node[i[1]] = value\n",
    "    elif i[1] in food_r:\n",
    "        if i[2] not in food_node.keys():\n",
    "            food_node[i[2]] = Node('food', name=i[2])\n",
    "        if i[1]=='宜吃食物' or i[1]=='推荐食谱':\n",
    "            r = Relationship(disease_node[disease_h], '适宜食材', food_node[i[2]])\n",
    "            r['name'] = '适宜食材'\n",
    "            all_re.append(r)\n",
    "        if i[1]=='忌吃食物' or i[1]=='禁忌食品':\n",
    "            r = Relationship(disease_node[disease_h], '不宜食材', food_node[i[2]])\n",
    "            r['name'] = '不宜食材'\n",
    "            all_re.append(r)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## add ACSM data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "metadata": {},
   "outputs": [],
   "source": [
    "acsm_data = pd.ExcelFile('./226.xlsx')\n",
    "entity_map = {}\n",
    "\n",
    "# disease_node = {}\n",
    "# food_node = {}\n",
    "# organ_node = {}\n",
    "# crowd_node = {}\n",
    "# alias_node = {}\n",
    "# drag_node = {}\n",
    "# keshi_node = {}\n",
    "\n",
    "sport_time = {}\n",
    "sport_freq = {}\n",
    "sport_type = {}\n",
    "sport_iten = {}\n",
    "\n",
    "entity_map = {\n",
    "    \"disease\": disease_node,\n",
    "    \"crowd\": crowd_node,\n",
    "    \"sport_time\": sport_time,\n",
    "    \"sport_freq\": sport_freq,\n",
    "    \"sport_type\": sport_type,\n",
    "    \"sport_iten\": sport_iten\n",
    "}\n",
    "\n",
    "c_to_e = {\n",
    "    \"疾病\": \"disease\",\n",
    "    \"人群\": \"crowd\",\n",
    "    \"运动强度\": \"sport_iten\",\n",
    "    \"运动频率\": \"sport_freq\",\n",
    "    \"运动类型\": \"sport_type\",\n",
    "    \"运动时间\": \"sport_time\"\n",
    "}\n",
    "\n",
    "def create_node(entities, properties, cur_type, entity_map):\n",
    "    entity_map = entity_map[cur_type]\n",
    "    if entities in entity_map.keys():\n",
    "        cur_entity = entity_map[entities]\n",
    "    else:\n",
    "        prop = {}\n",
    "        if properties != 'None':\n",
    "            prop = eval(properties)\n",
    "        cur_entity = Node(cur_type, name=entities, **prop)\n",
    "        entity_map[entities] = cur_entity\n",
    "    return cur_entity\n",
    "\n",
    "for sheet in acsm_data.sheet_names:\n",
    "    df = acsm_data.parse(sheet_name=sheet)\n",
    "    df = df.fillna(\"None\")\n",
    "    \n",
    "    head_entities = df['头实体'].values.tolist()\n",
    "    head_entities_types = [c_to_e[i] for i in df['头实体类型'].values.tolist()]\n",
    "    head_entities_properties = df['头实体属性'].values.tolist()\n",
    "    \n",
    "    tail_entities = df['尾实体'].values.tolist()\n",
    "    tail_entities_types = [c_to_e[i] for i in df['尾实体类型'].values.tolist()]\n",
    "    tail_entities_properties = df['尾实体属性'].values.tolist()\n",
    "    \n",
    "    relationships = df['关系'].values.tolist()\n",
    "    \n",
    "    for idx, line in enumerate(tail_entities):\n",
    "        if '~' in line:\n",
    "            tail_entities[idx] = line.replace('~', '～')\n",
    "    \n",
    "    for i in range(len(head_entities)):\n",
    "        if head_entities[i] == \"None\" or tail_entities[i] == \"None\":\n",
    "            continue\n",
    "            \n",
    "        cur_head_entity = create_node(head_entities[i], head_entities_properties[i], head_entities_types[i], entity_map)\n",
    "        cur_tail_entity = create_node(tail_entities[i], tail_entities_properties[i], tail_entities_types[i], entity_map)  \n",
    "        new_relationship = Relationship(cur_head_entity, relationships[i], cur_tail_entity)\n",
    "        new_relationship['name'] = relationships[i]\n",
    "        \n",
    "        all_re.append(new_relationship)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## add_nu"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.read_excel('./nu.xlsx')\n",
    "food_l = df['food'].values.tolist()\n",
    "nu_l = df['nu'].values.tolist()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "metadata": {},
   "outputs": [],
   "source": [
    "nu_nodes = {}\n",
    "\n",
    "nu_key = list(set(nu_l))\n",
    "for i in nu_key:\n",
    "    nu_nodes[i] = Node('nutrient', name=i)\n",
    "\n",
    "food_key = list(set(food_l))\n",
    "for f in food_key:\n",
    "    if f not in food_node.keys():\n",
    "        food_node[f] = Node('food', name=f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(:nutrient {name: '\\u86cb\\u767d\\u8d28'})\n"
     ]
    }
   ],
   "source": [
    "print(nu_node)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "metadata": {},
   "outputs": [],
   "source": [
    "for idx, f_d in enumerate(food_l):\n",
    "    f_node = food_node[f_d]\n",
    "    nu_node = nu_nodes[nu_l[idx]]\n",
    "    new_relationship = Relationship(f_node, '含有', nu_node)\n",
    "    new_relationship['name'] = '含有'\n",
    "    all_re.append(new_relationship)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## create graph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "326294\n"
     ]
    }
   ],
   "source": [
    "clear_graph(graph)\n",
    "print(len(all_re))\n",
    "create_relationship_by_subGraph(graph, all_re)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "322650\n"
     ]
    }
   ],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
